{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import our libraries\n",
    "import win32com.client\n",
    "import requests\n",
    "\n",
    "# get the active instance of Excel\n",
    "ExcelApp = win32com.client.GetActiveObject(\"Excel.Application\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define our API Key\n",
    "API_KEY = ExcelApp.Workbooks(1).Worksheets(\"Sheet2\").Range(\"A1\").Value\n",
    "\n",
    "# Define Base URL\n",
    "final_url = 'https://www.googleapis.com/youtube/v3/playlistItems'\n",
    "\n",
    "# Define search parameters\n",
    "PARAMETERS = {'part': 'snippet,contentDetails,status',\n",
    "              'maxResults': 5,\n",
    "              'playlistId':'PLcFcktZ0wnNlRhiXWzu7Mkn2GC72f2LXp',\n",
    "              'key': API_KEY}\n",
    "\n",
    "# make a request to the Youtube API\n",
    "response = requests.get(url = final_url, params = PARAMETERS)\n",
    "\n",
    "# decode our JSON String\n",
    "decoded_response = response.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def flatten_json(y):    \n",
    "    out = {}\n",
    "    \n",
    "    def flatten(x, name = ''):\n",
    "        if type(x) is dict:\n",
    "            for a in x:\n",
    "                flatten(x[a], name + a + '_')\n",
    "        elif type(x) is list:\n",
    "            i = 0\n",
    "            for a in x:\n",
    "                flatten(a, name + str(i) + '_')\n",
    "                i += 1\n",
    "        else:\n",
    "            out[name[:-1]] = x\n",
    "            \n",
    "    flatten(y)\n",
    "    return out\n",
    "\n",
    "# flatten the dictionary\n",
    "my_items = [flatten_json(video) for video in decoded_response['items']]\n",
    "\n",
    "# create a list of column headers\n",
    "column_headers = [key_value for key_value in my_items[0].keys()]\n",
    "\n",
    "# define the worksheet\n",
    "wrksht = ExcelApp.Workbooks(1).Worksheets(\"Sheet1\")\n",
    "\n",
    "# define the header section and populate it\n",
    "start_cell = wrksht.Cells(1,1)\n",
    "end_cell = wrksht.Cells(1, len(column_headers))\n",
    "wrksht.Range(start_cell, end_cell).Value = column_headers\n",
    "\n",
    "# get the row values\n",
    "row_values = [[my_list for my_list in video.values()] for video in my_items]\n",
    "\n",
    "# define the row section\n",
    "start_cell = wrksht.Cells(2, 1)\n",
    "end_cell = wrksht.Cells(1 + len(row_values), len(column_headers))\n",
    "\n",
    "# populate the row values\n",
    "wrksht.Range(start_cell, end_cell).Value = row_values\n",
    "wrksht.Range(start_cell, end_cell).WrapText = False"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}